package utils;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

/**
 * description: Excel 数据导入
 *
 * @author w
 * @version v1.0
 * @date 2020年3月30日下午9:46:27
 **/
public class ExcelImport {

    private ExcelImport INSTANSE = new ExcelImport();

    /**
     * excel 2003 suffix
     */
    private static final String EXCEL_XLS_SUFFIX = ".xls";

    /**
     * excel 2007 或以上 suffix
     */
    private static final String EXCEL_XLSX_SUFFIX = ".xlsx";

    /**
     * 分隔符 "."
     */
    public static final String POINT = ".";

    /**
     * description: 读取excel数据
     *
     * @param file
     * @return List<List < Object>>
     * @version v1.0
     * @author w
     * @date 2020年3月31日 下午3:36:39
     */
    public static List<List<Object>> importFile(File file) throws Exception {
        if (file == null) {
            return null;
        }
        if (file.getName().endsWith(EXCEL_XLS_SUFFIX)) {
            return readXls(new FileInputStream(file));
        }
        if (file.getName().endsWith(EXCEL_XLSX_SUFFIX)) {
            return readXlsx(new FileInputStream(file));
        }
        throw new RuntimeException("文件不对,必须是excel文件，后缀名以：" + EXCEL_XLS_SUFFIX + " 或者 " + EXCEL_XLSX_SUFFIX);
    }

    /**
     * description: 导入excel --- 支持web
     *
     * @param fileName
     * @param inputStream
     * @return List<List < Object>>
     * @throws Exception
     * @version v1.0
     * @author w
     * @date 2020年3月31日 下午4:51:01
     */
    public static List<List<Object>> importFile(MultipartFile multipartFile) throws Exception {
        if (multipartFile == null) {
            return null;
        }
        if (multipartFile.getOriginalFilename().endsWith(EXCEL_XLS_SUFFIX)) {
            return readXls(multipartFile.getInputStream());
        }
        if (multipartFile.getOriginalFilename().endsWith(EXCEL_XLSX_SUFFIX)) {
            return readXlsx(multipartFile.getInputStream());
        }
        throw new RuntimeException("文件不对,必须是excel文件，后缀名以：" + EXCEL_XLS_SUFFIX + " 或者 " + EXCEL_XLSX_SUFFIX);
    }


    /**
     * description: 读取03版excel
     *
     * @param file
     * @return List<List < Object>>
     * @version v1.0
     * @author w
     * @date 2020年3月31日 下午3:38:44
     */
    private static List<List<Object>> readXls(InputStream inputStream) throws Exception {
        List<List<Object>> list = new ArrayList<List<Object>>();
        // 读取excel
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        // 获取sheet 页数量
        int sheets = workbook.getNumberOfSheets();
        for (int num = 0; num < sheets; num++) {
            HSSFSheet sheet = workbook.getSheetAt(num);
            if (null == sheet) {
                continue;
            }
            // sheet 页的总行数
            int rows = sheet.getLastRowNum();
            // startRow 开始读取的行数 --- 第二行开始读
            for (int startRow = 1; startRow <= rows; startRow++) {
                HSSFRow row = sheet.getRow(startRow);
                List<Object> rowList = new ArrayList<Object>();
                if (null != row) {
                    // row 行中的 单元格总个数
                    short cells = row.getLastCellNum();
                    for (int x = 0; x <= cells; x++) {
                        HSSFCell cell = row.getCell(x);
                        if (null == cell) {
                            rowList.add("");
                        } else {
                            rowList.add(getXlsValue(cell));
                        }
                    }
                    list.add(rowList);
                }
            }
        }
        return list;
    }

    /**
     * description: 获取 03 版 excel数据
     *
     * @param cell
     * @return String
     * @version v1.0
     * @author w
     * @date 2020年3月31日 下午3:54:14
     */
    private static String getXlsValue(HSSFCell cell) {
        if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
            String cellValue = "";
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                cellValue = new SimpleDateFormat("yyyy/MM/dd").format(date);
            } else {
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(cell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
                if (strArr.equals("00")) {
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            // 其他类型的值，统一设置为 string
            // http://blog.csdn.net/ysughw/article/details/9288307
            cell.setCellType(Cell.CELL_TYPE_STRING);
            return String.valueOf(cell.getStringCellValue());
        }
    }

    /**
     * description: 读取07或以上版本的 excel
     *
     * @param file
     * @return List<List < Object>>
     * @throws Exception
     * @version v1.0
     * @author w
     * @date 2020年3月31日 下午4:01:25
     */
    private static List<List<Object>> readXlsx(InputStream inputStream) throws Exception {
        List<List<Object>> list = new ArrayList<List<Object>>();
        // 读取excel ，封装到 XSSFWorkbook 对象
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        int sheets = workbook.getNumberOfSheets();
        for (int num = 0; num < sheets; num++) {
            XSSFSheet sheet = workbook.getSheetAt(num);
            if (null == sheet) {
                continue;
            }
            // 获取sheet页的总行数
            int rows = sheet.getLastRowNum();
            for (int startRow = 1; startRow <= rows; startRow++) {
                // startRow 开始读取的行数， 从第二行开始读取
                XSSFRow row = sheet.getRow(startRow);
                List<Object> rowList = new ArrayList<Object>();
                if (null != row) {
                    // 获取行总单元格个数
                    short cells = row.getLastCellNum();
                    for (int x = 0; x < cells; x++) {
                        XSSFCell cell = row.getCell(x);
                        if (cell == null) {
                            rowList.add("");
                        } else {
                            rowList.add(getXlsxValue(cell));
                        }
                    }
                    list.add(rowList);
                }
            }
        }
        return list;
    }

    /**
     * description: 获取07或以上版本 excel 数据
     *
     * @param cell
     * @return Object
     * @version v1.0
     * @author w
     * @date 2020年3月31日 下午4:09:03
     */
    private static Object getXlsxValue(XSSFCell cell) {
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            String cellValue = "";
            if (DateUtil.isCellDateFormatted(cell)) {
                Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                cellValue = new SimpleDateFormat("yyyy/MM/dd").format(date);
            } else {
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(cell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
                if (strArr.equals("00")) {
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            // 其他类型的值，统一设置为 string
            // http://blog.csdn.net/ysughw/article/details/9288307
            //cell.setCellType(Cell.CELL_TYPE_STRING);
            return String.valueOf(cell.getStringCellValue());
        }
    }


    /**
     * Excel转为insert sql语句
     *  用户每月用水量信息（法人）_每月20日前更新
     * @param inputPath  excel文件路径
     * @param outputPath sql文件路径
     */
    public static void 用户每月用水量信息_法人(String inputPath,String outputPath){
        String path = inputPath;
        File file = new File(path);
        try {
            List<List<Object>> importFile = ExcelImport.importFile(file);
            System.out.println(importFile);
            System.out.println(importFile.size());

            File f = new File(outputPath);
            FileOutputStream fos1 = new FileOutputStream(f);
            OutputStreamWriter dos1 = new OutputStreamWriter(fos1);

            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO fryhmyysl (tyshxydm, yhh, yhdz, yhxz, frjgmc, cbrq, lsh,sqcj, sccbrq, sjly, bqcj, bqsl, znje, bjh, xzqydm) VALUES ");

            for (int i = 0; i < importFile.size(); i++) {
                if (importFile.get(i).size() != 0) {
                    sql.append("(");
                    for (int j = 0; j < 15; j++) {
                        if (j != 14) {
                            if ((j == 5 || j == 8) && importFile.get(i).get(j) != null) {
                                SimpleDateFormat format = new SimpleDateFormat("yyyy.MM.dd");
                                Date date = format.parse(importFile.get(i).get(j).toString());

                                SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                String time = format1.format(date);
                                sql.append("'" + time + "',");
                            } else {
                                sql.append("'" + importFile.get(i).get(j).toString() + "',");
                            }
                        } else {
                            if (i != importFile.size() - 1) {
                                sql.append("'" + importFile.get(i).get(j).toString() + "'),");
                            } else {
                                sql.append("'" + importFile.get(i).get(j).toString() + "');");
                            }
                        }
                    }
                }
            }
            System.out.println(sql.toString());
            dos1.write(sql.toString());
            dos1.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * Excel转为insert sql语句
     * 用水单位基本信息（法人）_每月20日前更新
     * @param inputPath  excel文件路径
     * @param outputPath sql文件路径
     */
    public static void 用水单位基本信息_法人(String inputPath,String outputPath){
        String path = inputPath;
        File file = new File(path);
        try {
            List<List<Object>> importFile = ExcelImport.importFile(file);
            System.out.println(importFile);
            System.out.println(importFile.size());

            File f = new File(outputPath);
            FileOutputStream fos1 = new FileOutputStream(f);
            OutputStreamWriter dos1 = new OutputStreamWriter(fos1);

            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO frysdw (syndyszl, syndfy, lszyss, khrq, sjly, yhmc, yhh,yhdz, yhxz, yhzt, tyshxydm, lxr, lxdh, xzqydm) VALUES ");

            for (int i = 0; i < importFile.size(); i++) {
                if (importFile.get(i).size() != 0) {
                    sql.append("(");
                    for (int j = 0; j < 14; j++) {
                        if (j != 13) {
                            if ((j == 3) && importFile.get(i).get(j) != null) {
                                SimpleDateFormat format = new SimpleDateFormat("yyyy.MM.dd");
                                Date date = format.parse(importFile.get(i).get(j).toString());

                                SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                String time = format1.format(date);
                                sql.append("'" + time + "',");
                            } else {
                                sql.append("'" + importFile.get(i).get(j).toString() + "',");
                            }
                        } else {
                            if (i != importFile.size() - 1) {
                                sql.append("'" + importFile.get(i).get(j).toString() + "'),");
                            } else {
                                sql.append("'" + importFile.get(i).get(j).toString() + "');");
                            }
                        }
                    }
                }
            }
            System.out.println(sql.toString());
            dos1.write(sql.toString());
            dos1.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * Excel转为insert sql语句
     * 公共供水企业供水数据_每月20日前更新
     * @param inputPath  excel文件路径
     * @param outputPath sql文件路径
     */
    public static void 公共供水企业供水数据(String inputPath,String outputPath){
        String path = inputPath;
        File file = new File(path);
        try {
            List<List<Object>> importFile = ExcelImport.importFile(file);
            System.out.println(importFile);
            System.out.println(importFile.size());

            File f = new File(outputPath);
            FileOutputStream fos1 = new FileOutputStream(f);
            OutputStreamWriter dos1 = new OutputStreamWriter(fos1);

            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO gggsqy (tyshxydm, frjgmc, xzqydm, gszl, sszl, czjmss, nzjmss,fwyss, gyss, qtgsss, qtss, sqsjcsr, sjcsr, sjgjr) VALUES ");

            for (int i = 0; i < importFile.size(); i++) {
                if (importFile.get(i).size() != 0) {
                    sql.append("(");
                    for (int j = 0; j < 14; j++) {
                        if (j != 13) {
                            if ((j == 11 || j == 12) && importFile.get(i).get(j) != null) {
                                SimpleDateFormat format = new SimpleDateFormat("yyyy.MM.dd");
                                Date date = format.parse(importFile.get(i).get(j).toString());

                                SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                String time = format1.format(date);
                                sql.append("'" + time + "',");
                            } else {
                                if (importFile.get(i).get(j).toString().equals("/")) {
                                    sql.append(null + ",");
                                } else {
                                    sql.append("'" + importFile.get(i).get(j).toString() + "',");
                                }
                            }
                        } else {
                            if (i != importFile.size() - 1) {
                                SimpleDateFormat format = new SimpleDateFormat("yyyy.MM.dd");
                                Date date = format.parse(importFile.get(i).get(j).toString());

                                SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                String time = format1.format(date);
                                sql.append("'" + time + "'),");
                            } else {
                                SimpleDateFormat format = new SimpleDateFormat("yyyy.MM.dd");
                                Date date = format.parse(importFile.get(i).get(j).toString());

                                SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                String time = format1.format(date);
                                sql.append("'" + time + "');");
                            }
                        }
                    }
                }
            }
            System.out.println(sql.toString());
            dos1.write(sql.toString());
            dos1.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 批量建表
     * excl转为建表sql语句
     * @param inputPath  excel文件路径
     * @param outputPath sql文件路径
     */
    public static void creatTable(String inputPath,String outputPath){
        String path = inputPath;
        File file = new File(path);
        try {
            List<List<Object>> importFile = ExcelImport.importFile(file);
            System.out.println(importFile);
            System.out.println(importFile.size());

            for (int i = 0; i < importFile.size(); i++) {
                System.out.println(importFile.get(i) + "\n");
            }


            File f = new File(outputPath);
            FileOutputStream fos1 = new FileOutputStream(f);
            OutputStreamWriter dos1 = new OutputStreamWriter(fos1);

            StringBuilder sql = new StringBuilder();

            /**
             * 解析出数据，拼接sql
             */

            /**
             * sql 模板
             * SET NAMES utf8mb4;
             * SET FOREIGN_KEY_CHECKS = 0;
             * DROP TABLE IF EXISTS `obj_bas`;
             * CREATE TABLE `obj_bas`  (
             *   `BAS_CODE` varchar(18) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '流域代码',
             *   `BAS_NAME` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '流域名称',
             *   `LOW_LEFT_LONG` decimal(9, 6) NOT NULL COMMENT '左下角经度',
             *   `LOW_LEFT_LAT` decimal(8, 6) NOT NULL COMMENT '左下角纬度',
             *   `UP_RIGHT_LONG` decimal(9, 6) NOT NULL COMMENT '右上角经度',
             *   `UP_RIGHT_LAT` decimal(8, 6) NOT NULL COMMENT '右上角纬度',
             *   `CR_OVER_TYPE` varchar(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '跨界类型',
             *   `BAS_AD_NAME` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '流域所属行政区',
             *   `BAS_GRAD` varchar(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '流域级别',
             *   `BAS_AREA` decimal(10, 2) NULL DEFAULT NULL COMMENT '流域面积',
             *   `NOTE` varchar(256) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '备注',
             *   `EFF_DATE` datetime NOT NULL COMMENT '记录生效时间',
             *   `EXPR_DATE` datetime NULL DEFAULT NULL COMMENT '记录失效时间',
             *   PRIMARY KEY (`BAS_CODE`, `EFF_DATE`) USING BTREE
             * ) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '流域名录表' ROW_FORMAT = Dynamic;
             *
             * SET FOREIGN_KEY_CHECKS = 1;
             */

            /**
             * 以一张表为单位，存到list数组中
             */
            ArrayList<ArrayList<ArrayList<Object>>> tableList = new ArrayList<ArrayList<ArrayList<Object>>>();

            // 计算出list有多少张表
            ArrayList<Long> tableAttributes = new ArrayList<Long>();
//            tableAttributes.add(Long.valueOf(0));
            Long tableIndex = Long.valueOf(1);
            for (int i = 0; i < importFile.size(); i++) {
                if (!importFile.get(i).get(0).toString().equals("")){
                    if (i!=0){
                        tableAttributes.add(tableIndex);
                    }
                    tableIndex = Long.valueOf(1);
                    continue;
                }
                tableIndex++;
            }

            // 定义startIndex、endIndex下标，用于取Excel表中的数据
            Long startIndex = Long.valueOf(0);
            Long endIndex = Long.valueOf(0);
            // 循环取字段
            for (int i = 0; i < tableAttributes.size(); i++) {
                sql.append("SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS `");
                //对下标值做第一次初始赋值
                endIndex = endIndex+tableAttributes.get(i);
                startIndex = endIndex-(tableAttributes.get(i));
                // 初始化表备注
                String tableRemark = null;
                // 记录主键字段
                ArrayList<String> key = new ArrayList<String>();
                // 以表为单位循环,并计算出起始下标
                for (int j = 0; j < tableAttributes.get(i); j++) {
                    // 以字段为单位循环
                    if (j == 0){
                        // 当j==0时需要特殊取出表名、以及表备注
                        // 备注
                        tableRemark = importFile.get((startIndex.intValue())+j).get(0).toString();
                        // 表名
                        sql.append(importFile.get((startIndex.intValue())+j).get(1)+"`;CREATE TABLE `"+importFile.get((startIndex.intValue())+j).get(1)+"` (");
                        //设置字段
                        sql.append("`"+importFile.get((startIndex.intValue())+j).get(3)+"` "+importFile.get((startIndex.intValue())+j).get(4));
                        // 判断字段是否非空
                        if (importFile.get((startIndex.intValue())+j).get(5).toString().equals("N")){
                            sql.append(" NOT NULL COMMENT '"+importFile.get((startIndex.intValue())+j).get(2)+"',");
                        }else {
                            sql.append(" NULL DEFAULT NULL COMMENT '"+importFile.get((startIndex.intValue())+j).get(2)+"',");
                        }
                        // 判断字段是否是主键字段
                        if(importFile.get((startIndex.intValue())+j).get(6).toString().equals("Y")){
                            key.add("`"+importFile.get((startIndex.intValue())+j).get(3).toString()+"`");
                        }
                    }else {
                        // 设置字段
                        sql.append("`"+importFile.get((startIndex.intValue())+j).get(3)+"` "+importFile.get((startIndex.intValue())+j).get(4));
                        // 判断字段是否非空
                        if (importFile.get((startIndex.intValue())+j).get(5).toString().equals("N")){
                            sql.append(" NOT NULL COMMENT '"+importFile.get((startIndex.intValue())+j).get(2)+"',");
                        }else {
                            sql.append(" NULL DEFAULT NULL COMMENT '"+importFile.get((startIndex.intValue())+j).get(2)+"',");
                        }
                        // 判断字段是否是主键字段
                        if(importFile.get((startIndex.intValue())+j).get(6).toString().equals("Y")){
                            key.add("`"+importFile.get((startIndex.intValue())+j).get(3).toString()+"`");
                        }
                    }
                    // 设置主键key
                    if (j == (tableAttributes.get(i).intValue()-1)){
                        StringBuffer primaryKey = new StringBuffer();
                        for (int k = 0; k < key.size(); k++) {
                            if (k < key.size()-1){
                                primaryKey.append(key.get(k)+",");
                            }else {
                                primaryKey.append(key.get(k));
                            }
                        }
                        sql.append("PRIMARY KEY ("+primaryKey+") USING BTREE");
                    }
                }
                // 将表备注加入到sql中
                sql.append(") ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '"+tableRemark+"' ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;\n");
            }
            System.out.println("每张表的字段数量："+tableAttributes);
            System.out.println(tableAttributes.size());


            /**
             * 控制台输出生成的建表sql语句
             */
            System.out.println(sql.toString());
            /**
             * 将sql文件写入text文档
             */
            dos1.write(sql.toString());
            /**
             * 关闭数据流
             */
            dos1.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
//        公共供水企业供水数据("D:\\User\\Admin\\Desktop\\文本文件\\公共供水数据表格\\2-2\\2022年1月-2023年3月_数据校验\\公共供水企业供水信息.xlsx",
//                "D:\\User\\Admin\\Desktop\\文本文件\\公共供水数据表格\\2-2\\2022年1月-2023年3月_数据校验\\公共供水企业供水信息(水业集团).txt");

        creatTable("D:\\User\\Admin\\Desktop\\文本文件\\数字孪生\\数据测试文件夹\\批量建库数据测试.xlsx",
                "D:\\User\\Admin\\Desktop\\文本文件\\数字孪生\\数据测试文件夹\\生成的建表sql文件.txt");
    }

}
